Oracle 如何根据 SQL_TEXT 生成 SQL_ID

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle SQL_ID 相关的八卦知识，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！

本文纯属八卦，基本没有任何实用价值。

Oracle 总是都会通过 SQL_ID 来标志一个唯一的 SQL。SQL_ID 与 SQL_TEX T一一对应。如果两个 SQL 文本有任何不同，包括空格等任何不可见字符，都会导致 SQL_ID 不同。

本文八卦的内容是：Oracle如何根据SQL_TEXT内容散列成一个13位的字符串。为什么这个字符串会是13位？为什么这个字符经常以数字开头？

目录 

• 0. 概述

• 1. 详细过程

• 1.1 示例

•   1.2 SQL_ID计算的详细过程

• 1.2.1 散列值的计算

• 1.2.2 取低64位整数

• 1.2.3 Base32转换为可见字符

• 2 代码片段

• 2.1 Perl

• 2.2 PHP

• 3. 最后

• 4.个人补充

0. 概述

Oracle 先计算 SQL_TEXT 的 md5 散列值；取散列值的低64位(bits)，每次取5位(最后一次4位)，使用 Base32 将其依次转换成可见字符，就是你最终看到的 SQL_ID。原理就是这样。

不过实际转换过程中有一些要注意的事项：

(a) Oracle 在计算 md5 散列时，会在 SQL_TEXT 末尾加一个不可见字符 \0，AWR 报表中经常有这样的 SQL_TEXT

(b) 注意 little-endian 的问题

(c) Base32 转码的可见字符为 0123456789abcdfghjkmnpqrstuvwxyz

(d)编写程序的时候需要注意大数精度的问题，本文中 Perl/PHP 程序都使用了数学大数处理函数

1. 详细过程

1.1 示例

我们考虑如下给定SQL：

select sysdate from dual;

在 Oracle 11g 中执行并查询v$SQL，可以看到这个SQL的SQL_ID是

SQL > select sql_id, hash_value from v$sql
2 where sql_text = 'select sysdate from dual';

SQL_ID HASH_VALUE
------------- ----------
7h35uxf5uhmm1 2343063137

1.2 SQL_ID 计算的详细过程

1.2.1 散列值的计算

将 SQL_TEXT 末尾加上一个空字符\0，然后进行 md5 散列：

use Digest::MD5 qw(md5 md5_hex md5_base64);
$stmt = "select sysdate from dual\0";
$hash = md5 $stmt;

select sysdate from dual\0 的 MD5 散列值为 abd4dbb3096b15f1ebba0c78614ea88b，共 128 位(明明是 32位，怎么说 128 位??？我也想知道，可惜链接无法访问了)，取低64位为："ebba0c78 614ea88b"。

md5散列的字节码如下(128位)：

|10101011|11010100|11011011|10110011|
|00001001|01101011|00010101|11110001|

|11101011|10111010|00001100|01111000|
|01100001|01001110|10101000|10001011|

1.2.2 取低64位整数

md5 散列值的低 64 位为：

|11101011|10111010|00001100|01111000|
|01100001|01001110|10101000|10001011|

分为两部分，高 32 位和低 32 位，分别为：ebba0c78 614ea88b，对应二进制字节流为：|11101011|10111010|00001100|01111000| 和 |01100001|01001110|10101000|10001011|。可以直接使用 unpack 函数将散列值解开。这里需要注意，取模运算为整数运算，而我这里的环境是 x86_64 little-endian，所以取模运算时对应的整数字节序(人读取的时候)：

|01111000|00001100|10111010|11101011|
|10001011|10101000|01001110|01100001|

perl 代码：

my($a,$b,$msb,$lsb) = unpack("V*",$hash);

1.2.3 Base32 转换为可见字符

Oracle 使用了 Base32 将字节流转换为可见字符。

一个 Base32 字符对应字节流的 5 位(bits)，这里总计 64 位，所以是 64/5，一共 13 个字符。其中 12 个字符为 5 位，有一个字符总是四位( SQL_ID 的第一位)。

我们来看本案例的字节流，每五位转换为一个 Base32 的编码，取最后 5 为 00001(十进制1)，对应 Base32 编码为 1；取倒数第二个五位 10011 (十进制19)，取倒数第三个五位为010011(十进制19)...

Oracle 使用的 Base32 对应编码字符为：

0123456789abcdfghjkmnpqrstuvwxyz

编码和字符对应关系

编码 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15
字符 0 1 2 3 4 5 6 7 8 9 a b c d f g
-----------------------------------------------------
编码 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
字符 h j k m n p q r s t u v w x y z

所以，上面编码 1、19、19 对应的字符为 1、m、m，这也正是 SQL_ID 对应的最后三位：

7h35uxf5uhmm1

2 代码片段

程序代码如下：

2.1 Perl

#!/usr/bin/perl -w
use Digest::MD5 qw(md5 md5_hex md5_base64);
use Math::BigInt;
my $stmt = "select sysdate from dual\0";
my $hash = md5 $stmt;
my($a,$b,$msb,$lsb) = unpack("V*",$hash);
my $sqln = $msb*(2**32)+$lsb;
my $stop = log($sqln) / log(32) + 1;
my $sqlid = '';
my $charbase32 = '0123456789abcdfghjkmnpqrstuvwxyz';
my @chars = split '', $charbase32;
for($i=0; $i < $stop-1; $i++){
my $x = Math::BigInt->new($sqln);
my $seq = $x->bdiv(32**$i)->bmod(32);
$sqlid = $chars[$seq].$sqlid;
}
print "SQL is:\n $stmt \nSQL_ID is\n $sqlid\n";

2.2 PHP

function stmt_2_sqlid($stmt){
$h = md5($stmt."\0",TRUE);
$un = unpack("V*",$h);
$msb = $un[3] + 0; if($msb < 0) {$msb = pow(2,32) + $msb;}
$lsb = $un[4] + 0; if($lsb < 0) {$lsb = pow(2,32) + $lsb;}
$sqln = bcadd(bcmul($msb , bcpow(2,32)) , $lsb);
$stop = log($sqln) / log(32) + 1;
$sqlid = '';
$alphabet = '0123456789abcdfghjkmnpqrstuvwxyz';
for($i = 0; $i < $stop-1; $i++){
$seq = bcmod((bcdiv($sqln,bcpow(32,$i),5)),32);
$sqlid = $alphabet[$seq].$sqlid;
}
return $sqlid;
}
$stmt = 'select sysdate from dual';
echo stmt_2_sqlid($stmt);

3. 最后

一个略有趣的事实，SQL_ID 的第一位经常会是数字。这是因为是64位(bits)，按照5位一个字符划分，最后一个字符总是只有4位，范围总是0到15，对应字符为 0123456789abcdfg，也就是说超过 50% 的 SQL_ID 都是以数字开头的。

4.个人补充

Oracle 10g 提供了一个包 dbms_utility.SQLID_TO_SQLHASH 可以将 sql_id 转换成 hash_value 的方法：

SQL> select sysdate from dual;

SYSDATE
-------------------
2021-11-18 15:16:32

Elapsed: 00:00:00.01
SQL> select kglnahsv, kglnahsh from x$kglob where kglnaobj ='select sysdate from dual';

KGLNAHSV                           KGLNAHSH
-------------------------------- ----------
b3dbd4abf1156b09780cbaeb8ba84e61 2343063137
b3dbd4abf1156b09780cbaeb8ba84e61 2343063137

Elapsed: 00:00:00.20
SQL> select sql_id, hash_value, old_hash_value from v$sql where sql_text ='select sysdate from dual';

SQL_ID        HASH_VALUE OLD_HASH_VALUE
------------- ---------- --------------
7h35uxf5uhmm1 2343063137     3742653144

Elapsed: 00:00:00.21
SQL> select dbms_utility.SQLID_TO_SQLHASH('7h35uxf5uhmm1') hash_value FROM DUAL;

HASH_VALUE
----------
2343063137

10g 以后，sql_id 和 hash_value 他们的关系是这样的：

1)oracle 用 MD5 算法对 library cache obj 进行哈希，生成一个 128bit 的 hash value，也就是 KGLNAHSV(16进制).

2)KGLNAHSV 的低 64 bit 作为 SQL_ID(32进制).

3)KGLNAHSV 的低 32 bit作为 HASH_VALUE(10进制)

因为 10g+的哈希算法变了，所以 10g 之前的 hash_value 其实 v$sql.old_hash_value。

好了，今日八卦结束。

---

原文链接如下

https://www.orczhou.com/index.php/2013/03/how-oracle-gen-sql-id-from-sql-text/

https://www.gxlcms.com/sql_question-448085.html

本次分享到此结束啦~

❤️ 欢迎关注我的公众号，来一起玩耍吧！！！

——————————————————————--—--————

公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107

————————————————————————----———